﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DAL.Entity;
using DAL.Persistencia;

namespace DAL.Persistencia
{
    public class TBItineraryDAL
    {

        private NewTierraEntities Con;

        public TBItineraryDAL()
        {
            Con = new NewTierraEntities();
        }

        public void Salvar(TBItinerary t)
        {
            try
            {
                Con.AddToTBItinerary(t);
                Con.SaveChanges();
            }
            catch 
            {                
                throw;
            }
        }

        public List<TBItinerary> ListarTodosCode(string Code)
        {
            try
            {
                //return Con.TBItinerary.Where(t => t.Code.Equals(Code)).OrderBy(t => t.DateFrom).ToList();

                return (from tbi in Con.TBItinerary
                        where tbi.Code.Equals(Code)
                        orderby tbi.DateFrom, tbi.Ordem
                        select tbi).ToList();
            }
            catch 
            {                
                throw;
            }
        }

        public List<TBItinerary> ListarTodosCode(string Code, int Ordem)
        {
            try
            {
                //return Con.TBItinerary.Where(t => t.Code.Equals(Code)).OrderBy(t => t.DateFrom).ToList();

                return (from tbi in Con.TBItinerary
                        where tbi.Code.Equals(Code)
                        orderby tbi.DateFrom, tbi.Ordem
                        select tbi).ToList();
            }
            catch
            {
                throw;
            }
        }

        public List<TBItinerary> ListarTodosHOTEL(string Code)
        {
            try
            {
                return Con.TBItinerary.Where(t => t.Code.Equals(Code) && t.Flag.Equals("HOTEL") ||
                                                  t.Code.Equals(Code) && t.Flag.Equals("MEAL")).OrderBy(t => t.DateFrom).ToList();
            }
            catch
            {
                throw;
            }
        }

        public List<TBItinerary> ListarTodosTRANSFER(string Code)
        {
            try
            {
                return Con.TBItinerary.Where(t => t.Code.Equals(Code) && t.Flag.Equals("TRANSFER") ||
                                                  t.Code.Equals(Code) && t.Flag.Equals("SubServ")).OrderBy(t => t.DateFrom).ToList();
            }
            catch
            {
                throw;
            }
        }

        public List<TBItinerary> ListarTodosTOUR(string Code)
        {
            try
            {
                return Con.TBItinerary.Where(t => t.Code.Equals(Code) && t.Flag.Equals("TOUR")).OrderBy(t => t.DateFrom).ToList();
            }
            catch
            {
                throw;
            }
        }

        public List<TBItinerary> ListarTodosEXTRA(string Code)
        {
            try
            {
                return Con.TBItinerary.Where(t => t.Code.Equals(Code) && t.Flag.Equals("EXTRA")).OrderBy(t => t.DateFrom).ToList();
            }
            catch
            {
                throw;
            }
        }

        public List<TBItinerary> ObterPorQuote(string Quote)
        {
            try
            {
                return Con.TBItinerary.Where(t => t.Quotation_Code.Equals(Quote)).ToList();
            }
            catch
            {                
                throw;
            }
        }

        public void Excluir(TBItinerary t)
        {
            try
            {
                Con.TBItinerary.DeleteObject(t);
                Con.SaveChanges();
            }
            catch 
            {                
                throw;
            }
        }

        public TBItinerary ObterPorId(int IdTbItine)
        {
            try
            {
                return Con.TBItinerary.Where(t => t.Itinerary_id == IdTbItine).SingleOrDefault();
            }
            catch 
            {                
                throw;
            }
        }

        public void Atualizar(TBItinerary novo, int numRange)
        {
            try
            {

                TBItinerary antigo = ObterPorId(novo.Itinerary_id);

                switch (numRange)
                {
                    case 1:
                        antigo.Range01 = novo.Range01;
                        antigo.Base01 = novo.Base01;
                        break;
                    case 2:
                        antigo.Range02 = novo.Range02;
                        antigo.Base02 = novo.Base02;
                        break;
                    case 3:
                        antigo.Range03 = novo.Range03;
                        antigo.Base03 = novo.Base03;
                        break;
                    case 4:
                        antigo.Range04 = novo.Range04;
                        antigo.Base04 = novo.Base04;
                        break;
                    case 5:
                        antigo.Range05 = novo.Range05;
                        antigo.Base05 = novo.Base05;
                        break;
                    case 6:
                        antigo.Range06 = novo.Range06;
                        antigo.Base06 = novo.Base06;
                        break;
                    case 7:
                        antigo.Range07 = novo.Range07;
                        antigo.Base07 = novo.Base07;
                        break;
                    case 8:
                        antigo.Range08 = novo.Range08;
                        antigo.Base08 = novo.Base08;
                        break;
                }

                Con.SaveChanges();
            }
            catch 
            {                
                throw;
            }
        }

        public void Atualizar(string range, string bse, decimal val, string bases, int IdItinerary)
        {
            SqlCon cn = new SqlCon();
            try
            {
                string valor = val.ToString().Replace(',', '.');

                string sql = "UPDATE TBItinerary SET ";
                sql = sql + range + " = " + valor + ", ";
                sql = sql + bse + " = '" + bases + "'";
                sql = sql + " where Itinerary_id = " + IdItinerary;

                cn.AbrirConexao();
                cn.Cmd = new SqlCommand(sql, cn.Con);
                cn.Cmd.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                cn.FecharConexao();
            }
        }

        public void AtualizarDAY(TBItinerary novo)
        {
            try
            {
                TBItinerary antigo = ObterPorId(novo.Itinerary_id);

                antigo.DAY = novo.DAY;

                Con.SaveChanges();

            }
            catch 
            {                
                throw;
            }
        }

        public void AtualizarORDEM(TBItinerary novo)
        {
            try
            {
                TBItinerary antigo = ObterPorId(novo.Itinerary_id);

                antigo.Ordem = novo.Ordem;

                Con.SaveChanges();

            }
            catch
            {
                throw;
            }
        }

        public int QtdRooms(string Code)
        {
            try
            {
                return Con.TBItinerary.Where(a => a.Code.Equals(Code) &&
                                                  a.Room != null).Select(a => a.Room).Distinct().Count();
            }
            catch
            {                
                throw;
            }
        }

        public List<string> ObterRoomsCode(string Code)
        {
            try
            {

                return Con.TBItinerary.Where(a => a.Code.Equals(Code) &&
                                                  a.Room != null).Select(a => a.Room).Distinct().ToList();
            }
            catch 
            {                
                throw;
            }
        }

    }
}
